package com.egao.orderB.service.impl;

import cn.hutool.core.collection.CollUtil;
import com.egao.common.core.Constants;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.DateUtil;
import com.egao.common.core.damon.utils.SszgUtils;
import com.egao.common.core.damon.utils.Utils;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.entity.User;
import com.egao.orderB.biz.ApplyCustomsFeeBiz;
import com.egao.orderB.entity.ApplyCustoms;
import com.egao.orderB.entity.ApplyCustomsFee;
import com.egao.orderB.service.ApplyCustomsFeeService;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

//import com.egao.orderB.mapper.ApplyCustomsFeeMapper;
//import javax.transaction.Transactional;

/**
 * 报关费用服务实现类
 * @author : Doman
 * @date : 2021-09-08 14:39:54
 * @version ：1.0
 */
@Service
public class ApplyCustomsFeeServiceImpl /*extends ServiceImpl<ApplyCustomsFeeMapper, ApplyCustomsFee> */implements ApplyCustomsFeeService {

	@Autowired
	private ApplyCustomsFeeBiz applyCustomsFeeBiz;

  @Override
  public JsonResult list(PageResult2<T> page, ApplyCustomsFee applyCustomsFee, User loginUser)  throws Exception{
      return applyCustomsFeeBiz.list(page,applyCustomsFee,loginUser);
  }

  @Override
  public JsonResult list(ApplyCustomsFee applyCustomsFee, User loginUser) {
      return JsonResult.ok().setData(applyCustomsFeeBiz.list(applyCustomsFee,loginUser));
  }

  @Override
  public List<String> feeNames(ApplyCustoms applyCustoms, List<String> feeNames, User loginUser) {
    return applyCustomsFeeBiz.feeNames(applyCustoms, feeNames, loginUser);
  }

  @Override
  public JsonResult getById(ApplyCustomsFee applyCustomsFee) {
      return JsonResult.ok().setData(applyCustomsFeeBiz.getById(applyCustomsFee));
  }

  @Transactional
  @Override
  public JsonResult save(ApplyCustomsFee applyCustomsFee, User loginUser) {
      if (applyCustomsFeeBiz.save(applyCustomsFee, loginUser)) {
          return JsonResult.ok("添加成功");
      }
      return JsonResult.error("添加失败");
  }

  @Transactional
  @Override
  public JsonResult update(ApplyCustomsFee applyCustomsFee, User loginUser) {
      if (applyCustomsFeeBiz.update(applyCustomsFee, loginUser)) {
          return JsonResult.ok("修改成功");
      }
      return JsonResult.error("修改失败");
  }

  @Transactional
  @Override
  public JsonResult remove(ApplyCustomsFee applyCustomsFee, User loginUser) {
      if (applyCustomsFeeBiz.remove(applyCustomsFee, loginUser)) {
          return JsonResult.ok("删除成功");
      }
      return JsonResult.error("删除失败");
  }

  @Override
  public JsonResult exportsFinance(List<ApplyCustoms> list, List<String> feeNamesys, List<String> feeNamesyf, ApplyCustoms applyCustoms2) throws Exception{
    //https://www.cnblogs.com/janson071/p/10119935.html
    //https://www.cnblogs.com/pejsidney/p/8383843.html
    //创建工作簿
    XSSFWorkbook xssfWorkbook = null;
    xssfWorkbook = new XSSFWorkbook();

    //创建工作表
    XSSFSheet xssfSheet;
    xssfSheet = xssfWorkbook.createSheet();

    //创建行
    XSSFRow xssfRow;

    //创建列，即单元格Cell
    XSSFCell xssfCell;

    xssfRow = xssfSheet.createRow(0);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格*

    XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    Font fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 18); // 大小
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

    xssfRow = xssfSheet.createRow(1);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 12); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

    xssfRow = xssfSheet.createRow(2);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.LEFT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    xssfCell.setCellStyle(cellStyle);
    String date2 = "";
    if (Utils.isNotEmpty(applyCustoms2.getDate2())) {
      date2 = " 到 "+applyCustoms2.getDate2().substring(0, 10);
    }
    xssfCell.setCellValue("账期："+applyCustoms2.getDate1().substring(0,10)+date2); //设置单元格内容

    List<String> headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱号",
            "报关行","操作员","应收明细");
    headRow.addAll(feeNamesys);
    headRow.remove(headRow.size()-1);
    headRow.add("应收合计");
    headRow.add("应付明细");
    headRow.addAll(feeNamesyf);
    headRow.remove(headRow.size()-1);
    headRow.add("应付合计");
    headRow.add("利润");
    xssfRow = xssfSheet.createRow(3);
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

    headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱号",
            "报关行","操作员");
    headRow.addAll(feeNamesys);
    headRow.add("应收合计");
    headRow.addAll(feeNamesyf);
    headRow.add("应付合计");
    headRow.add("利润");
    xssfRow = xssfSheet.createRow(4);
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

    String[] letters = new String[]{"H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
            "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
            "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
            "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB",
            "CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT",
            "CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL",
            "DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ"};

    //把List里面的数据写到excel中
    for (int i = 0; i < list.size(); i++) {
      ApplyCustoms applyCustoms = list.get(i);
      List<ApplyCustomsFee> applyCustomsFees = applyCustoms.getApplyCustomsFees();

      xssfRow = xssfSheet.createRow(i+5);
      //创建每个单元格Cell，即列的数据
      xssfCell = xssfRow.createCell(0); //创建单元格
      xssfCell.setCellValue(i+1); //设置单元格内容
      xssfCell = xssfRow.createCell(1); //创建单元格
      xssfCell.setCellValue(applyCustoms.getCustomer()); //设置单元格内容

      List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(applyCustoms.getCreateTime()),"MM-dd"),
              applyCustoms.getCustomer(), applyCustoms.getBillOfLading(), applyCustoms.getBoxNum(), applyCustoms.getCustomsBroker(),
              applyCustoms.getOperaterName()
      );
      cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
      fontStyle = xssfWorkbook.createFont(); // 字体样式
      fontStyle.setBold(false); // 加粗
      fontStyle.setFontName("宋体"); // 字体
      fontStyle.setFontHeightInPoints((short) 10); // 大小
      // 将字体样式添加到单元格样式中
      cellStyle.setFont(fontStyle);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);

      float temp_ret = 0;
      for(int t = 0; t < feeNamesys.size(); t ++) {
        String feeName = feeNamesys.get(t);
        temp_ret = 0;
        for (int j = 0; j < applyCustomsFees.size(); j ++) {
          ApplyCustomsFee cf = applyCustomsFees.get(j);
          if(cf.getType().equals("应收") && cf.getFeeName().equals(feeName)) {
            if(Utils.isNotEmpty(cf.getPrice())) {
              temp_ret += Float.parseFloat(cf.getPrice());
            }
          }
        }
        row.add(temp_ret + "");
      }
      row.add("function::SUM("+letters[0]+(i+6)+":"+letters[feeNamesys.size()-1]+(i+6)+")");

      for(int t = 0; t < feeNamesyf.size(); t ++) {
        temp_ret = 0;
        String feeName = feeNamesyf.get(t);
        for (int j = 0; j < applyCustomsFees.size(); j ++) {
          ApplyCustomsFee cf = applyCustomsFees.get(j);
          if (cf.getType().equals("应付") && cf.getFeeName().equals(feeName)) {
            if (Utils.isNotEmpty(cf.getPrice())) {
              temp_ret += Float.parseFloat(cf.getPrice());
            }
          }
        }
        row.add(temp_ret + "");
      }
      row.add("function::SUM("+letters[feeNamesys.size()+1]+(i+6)+":"+letters[feeNamesys.size()+feeNamesyf.size()]+(i+6)+")");
      float ysTotalNoTax = 0, yfTotalNoTax = 0;
      for (ApplyCustomsFee cf2 : applyCustomsFees) {
        if(Utils.isNotEmpty(cf2.getPrice())) {
          if (cf2.getType().equals("应收")) {
            float cc = Float.parseFloat(cf2.getPrice()) * Float.parseFloat(cf2.getExchangeRate());
            if (cf2.getInvoicestate().equals("1")) {
              cc = SszgUtils.getInvoiceNum(cc, cf2.getDriverName());
            }
            ysTotalNoTax += cc;
          } else {
            float cc = Float.parseFloat(cf2.getPrice()) * Float.parseFloat(cf2.getExchangeRate());
            if (cf2.getInvoicestate().equals("1")) {
              cc = SszgUtils.getInvoiceNum(cc, cf2.getDriverName());
            }
            yfTotalNoTax += cc;
          }
        }
      }
//      row.add(Utils.twoDot(ysTotalNoTax-yfTotalNoTax-10)+"");
      row.add("function::TRUNC(IMSUB("+letters[feeNamesys.size()]+(i+6)+","+letters[feeNamesys.size()+feeNamesyf.size()+1]+(i+6)+"),2)");
      String numColumns = ",";
      for(int t = 0; t < feeNamesys.size(); t ++) {
        numColumns += (t+7)+",";
      }
      for(int t = 0; t < feeNamesyf.size(); t ++) {
        numColumns += (t+8+feeNamesys.size())+",";
      }
      numColumns += 10+feeNamesys.size()+feeNamesyf.size()+",";

      setRow(xssfRow,row, cellStyle, numColumns);
    }

    // 合并单元格
    CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 7+3+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    cra =new CellRangeAddress(1, 1, 0, 7+3+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    cra =new CellRangeAddress(2, 2, 0, 7+3+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    if (feeNamesys.size() > 1) {
      cra = new CellRangeAddress(3, 3, 7, 6 + feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
      xssfSheet.addMergedRegion(cra);
    }
    if (feeNamesyf.size() > 1) {
      cra =new CellRangeAddress(3, 3, 8+feeNamesys.size(), 7+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
      xssfSheet.addMergedRegion(cra);
    }
    for(int i = 0; i <10+feeNamesys.size()+feeNamesyf.size(); i ++) {
      if(i < 7 || i == (7+feeNamesys.size()) || i > (7+feeNamesys.size()+feeNamesyf.size())){
        cra =new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
      }
    }

    xssfRow = xssfSheet.createRow(5+list.size());
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("合计:"); //设置单元格内容


    for(int i =0; i <= feeNamesys.size() + feeNamesyf.size() + 2; i ++) {
      xssfCell = xssfRow.createCell(7+i); //创建单元格
      fontStyle = xssfWorkbook.createFont(); // 字体样式
      fontStyle.setFontName("宋体"); // 字体
      fontStyle.setFontHeightInPoints((short) 10); // 大小
      cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 将字体样式添加到单元格样式中
      cellStyle.setFont(fontStyle);
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);
      xssfCell.setCellStyle(cellStyle);
      xssfCell.setCellType(CellType.NUMERIC);
      xssfCell.setCellFormula("SUM("+letters[i]+"6:"+letters[i]+(list.size()+5)+")"); //设置单元格内容
    }
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(18+feeNamesys.size() + feeNamesyf.size() + 7); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue(""); //设置单元格内容

    cra =new CellRangeAddress(5+list.size(), 5+list.size(), 0, 6); // 起始行, 终止行, 起始列, 终止列
    RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
    xssfSheet.addMergedRegion(cra);

    OutputStream os = new FileOutputStream(new File(Constants.UPLOAD_DIR + "file/finance.xlsx"));
    //用输出流写到excel
    xssfWorkbook.write(os);
    os.flush();
    os.close();

    return JsonResult.error("excel 生成成功!");
  }

  @Override
  public JsonResult exportsCustomer(List<ApplyCustoms> list, List<String> feeNamesys, List<String> feeNamesyf, ApplyCustoms applyCustoms2) throws Exception{
    //https://www.cnblogs.com/janson071/p/10119935.html
    //https://www.cnblogs.com/pejsidney/p/8383843.html
    //创建工作簿
    XSSFWorkbook xssfWorkbook = null;
    xssfWorkbook = new XSSFWorkbook();

    //创建工作表
    XSSFSheet xssfSheet;
    xssfSheet = xssfWorkbook.createSheet();

    //创建行
    XSSFRow xssfRow;

    //创建列，即单元格Cell
    XSSFCell xssfCell;

    xssfRow = xssfSheet.createRow(0);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格*

    XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    Font fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 18); // 大小
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

    xssfRow = xssfSheet.createRow(1);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 12); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

    xssfRow = xssfSheet.createRow(2);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.LEFT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    xssfCell.setCellStyle(cellStyle);
    String date2 = "";
    if (Utils.isNotEmpty(applyCustoms2.getDate2())) {
      date2 = " 到 "+applyCustoms2.getDate2().substring(0, 10);
    }
    xssfCell.setCellValue("账期："+applyCustoms2.getDate1().substring(0,10)+date2); //设置单元格内容

    List<String> headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱号","应收明细");
    headRow.addAll(feeNamesys);
    headRow.remove(headRow.size()-1);
    headRow.add("应收合计");
    xssfRow = xssfSheet.createRow(3);
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

    headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱号");
    headRow.addAll(feeNamesys);
    headRow.add("应收合计");
    xssfRow = xssfSheet.createRow(4);
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

    String[] letters = new String[]{"F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
            "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
            "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
            "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB",
            "CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT",
            "CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL",
            "DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ"};

    //把List里面的数据写到excel中
    for (int i = 0; i < list.size(); i++) {
      ApplyCustoms applyCustoms = list.get(i);
      List<ApplyCustomsFee> applyCustomsFees = applyCustoms.getApplyCustomsFees();

      xssfRow = xssfSheet.createRow(i+5);
      //创建每个单元格Cell，即列的数据
      xssfCell = xssfRow.createCell(0); //创建单元格
      xssfCell.setCellValue(i+1); //设置单元格内容
      xssfCell = xssfRow.createCell(1); //创建单元格
      xssfCell.setCellValue(applyCustoms.getCustomer()); //设置单元格内容

      List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(applyCustoms.getCustomsDate()),"MM-dd"),
              applyCustoms.getCustomer(), applyCustoms.getBillOfLading(), applyCustoms.getBoxNum());
      cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
      fontStyle = xssfWorkbook.createFont(); // 字体样式
      fontStyle.setBold(false); // 加粗
      fontStyle.setFontName("宋体"); // 字体
      fontStyle.setFontHeightInPoints((short) 10); // 大小
      // 将字体样式添加到单元格样式中
      cellStyle.setFont(fontStyle);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);

      float temp_ret = 0;
      for(int t = 0; t < feeNamesys.size(); t ++) {
        String feeName = feeNamesys.get(t);
        temp_ret = 0;
        for (int j = 0; j < applyCustomsFees.size(); j ++) {
          ApplyCustomsFee cf = applyCustomsFees.get(j);
          if(cf.getType().equals("应收") && cf.getFeeName().equals(feeName)) {
            if(Utils.isNotEmpty(cf.getPrice())) {
              temp_ret += Float.parseFloat(cf.getPrice());
            }
          }
        }
        row.add(temp_ret + "");
      }
      row.add("function::SUM("+letters[0]+(i+6)+":"+letters[feeNamesys.size()-1]+(i+6)+")");

      float ysTotalNoTax = 0, yfTotalNoTax = 0;
      for (ApplyCustomsFee cf2 : applyCustomsFees) {
        if(Utils.isNotEmpty(cf2.getPrice())) {
          if (cf2.getType().equals("应收")) {
            float cc = Float.parseFloat(cf2.getPrice()) * Float.parseFloat(cf2.getExchangeRate());
            if (cf2.getInvoicestate().equals("1")) {
              cc = SszgUtils.getInvoiceNum(cc, cf2.getDriverName());
            }
            ysTotalNoTax += cc;
          } else {
            float cc = Float.parseFloat(cf2.getPrice()) * Float.parseFloat(cf2.getExchangeRate());
            if (cf2.getInvoicestate().equals("1")) {
              cc = SszgUtils.getInvoiceNum(cc, cf2.getDriverName());
            }
            yfTotalNoTax += cc;
          }
        }
      }
//      row.add(Utils.twoDot(ysTotalNoTax-yfTotalNoTax-10)+"");
      String numColumns = ",";
      for(int t = 0; t < feeNamesys.size()+1; t ++) {
        numColumns += (t+5)+",";
      }
      setRow(xssfRow,row, cellStyle, numColumns);
    }

    // 合并单元格
    CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 5+0+feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    cra =new CellRangeAddress(1, 1, 0, 5+0+feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    cra =new CellRangeAddress(2, 2, 0, 5+0+feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    if (feeNamesys.size() > 1) {
      cra = new CellRangeAddress(3, 3, 5, 4 + feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
      xssfSheet.addMergedRegion(cra);
    }

    for(int i = 0; i <6+feeNamesys.size(); i ++) {
      if(i < 5 || i > (4+feeNamesys.size())){
        cra =new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
      }
    }

    xssfRow = xssfSheet.createRow(5+list.size());
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("合计:"); //设置单元格内容


    for(int i =0; i <= feeNamesys.size(); i ++) {
      xssfCell = xssfRow.createCell(5+i); //创建单元格
      fontStyle = xssfWorkbook.createFont(); // 字体样式
      fontStyle.setFontName("宋体"); // 字体
      fontStyle.setFontHeightInPoints((short) 10); // 大小
      cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 将字体样式添加到单元格样式中
      cellStyle.setFont(fontStyle);
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);
      xssfCell.setCellStyle(cellStyle);
      xssfCell.setCellType(CellType.NUMERIC);
      xssfCell.setCellFormula("SUM("+letters[i]+"6:"+letters[i]+(list.size()+5)+")"); //设置单元格内容
    }


    cra =new CellRangeAddress(5+list.size(), 5+list.size(), 0, 4); // 起始行, 终止行, 起始列, 终止列
    RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
    xssfSheet.addMergedRegion(cra);

    OutputStream os = new FileOutputStream(new File(Constants.UPLOAD_DIR + "file/finance.xlsx"));
    //用输出流写到excel
    xssfWorkbook.write(os);
    os.flush();
    os.close();

    return JsonResult.error("excel 生成成功!");
  }

  @Override
  public JsonResult exportsApplyCustoms(List<ApplyCustoms> list, List<String> feeNamesys, List<String> feeNamesyf, ApplyCustoms applyCustoms2) throws Exception{
    //https://www.cnblogs.com/janson071/p/10119935.html
    //https://www.cnblogs.com/pejsidney/p/8383843.html
    //创建工作簿
    XSSFWorkbook xssfWorkbook = null;
    xssfWorkbook = new XSSFWorkbook();

    //创建工作表
    XSSFSheet xssfSheet;
    xssfSheet = xssfWorkbook.createSheet();

    //创建行
    XSSFRow xssfRow;

    //创建列，即单元格Cell
    XSSFCell xssfCell;

    xssfRow = xssfSheet.createRow(0);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格*

    XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    Font fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 18); // 大小
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

    xssfRow = xssfSheet.createRow(1);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 12); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

    xssfRow = xssfSheet.createRow(2);
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.LEFT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    xssfCell.setCellStyle(cellStyle);
    String date2 = "";
    if (Utils.isNotEmpty(applyCustoms2.getDate2())) {
      date2 = " 到 "+applyCustoms2.getDate2().substring(0, 10);
    }
    xssfCell.setCellValue("账期："+applyCustoms2.getDate1().substring(0,10)+date2); //设置单元格内容

    List<String> headRow = CollUtil.newArrayList("序号","日期","报关行","提单号","箱号","应付明细");
    headRow.addAll(feeNamesyf);
    headRow.remove(headRow.size()-1);
    headRow.add("应付合计");
    xssfRow = xssfSheet.createRow(3);
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

    headRow = CollUtil.newArrayList("序号","日期","报关行","提单号","箱号");
    headRow.addAll(feeNamesyf);
    headRow.add("应付合计");
    xssfRow = xssfSheet.createRow(4);
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

    String[] letters = new String[]{"F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
            "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
            "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
            "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB",
            "CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT",
            "CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL",
            "DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ"};

    //把List里面的数据写到excel中
    for (int i = 0; i < list.size(); i++) {
      ApplyCustoms applyCustoms = list.get(i);
      List<ApplyCustomsFee> applyCustomsFees = applyCustoms.getApplyCustomsFees();

      xssfRow = xssfSheet.createRow(i+5);
      //创建每个单元格Cell，即列的数据
      xssfCell = xssfRow.createCell(0); //创建单元格
      xssfCell.setCellValue(i+1); //设置单元格内容
      xssfCell = xssfRow.createCell(1); //创建单元格
      xssfCell.setCellValue(applyCustoms.getCustomer()); //设置单元格内容

      List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(applyCustoms.getCreateTime()),"MM-dd"),
              applyCustoms.getCustomsBroker(), applyCustoms.getBillOfLading(), applyCustoms.getBoxNum());
      cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
      fontStyle = xssfWorkbook.createFont(); // 字体样式
      fontStyle.setBold(false); // 加粗
      fontStyle.setFontName("宋体"); // 字体
      fontStyle.setFontHeightInPoints((short) 10); // 大小
      // 将字体样式添加到单元格样式中
      cellStyle.setFont(fontStyle);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);

      float temp_ret = 0;
      for(int t = 0; t < feeNamesyf.size(); t ++) {
        temp_ret = 0;
        String feeName = feeNamesyf.get(t);
        for (int j = 0; j < applyCustomsFees.size(); j ++) {
          ApplyCustomsFee cf = applyCustomsFees.get(j);
          if (cf.getType().equals("应付") && cf.getFeeName().equals(feeName)) {
            if (Utils.isNotEmpty(cf.getPrice())) {
              temp_ret += Float.parseFloat(cf.getPrice());
            }
          }
        }
        row.add(temp_ret + "");
      }
      row.add("function::SUM("+letters[0]+(i+6)+":"+letters[feeNamesyf.size()-1]+(i+6)+")");

      float ysTotalNoTax = 0, yfTotalNoTax = 0;
      for (ApplyCustomsFee cf2 : applyCustomsFees) {
        if(Utils.isNotEmpty(cf2.getPrice())) {
          if (cf2.getType().equals("应收")) {
            float cc = Float.parseFloat(cf2.getPrice()) * Float.parseFloat(cf2.getExchangeRate());
            if (cf2.getInvoicestate().equals("1")) {
              cc = SszgUtils.getInvoiceNum(cc, cf2.getDriverName());
            }
            ysTotalNoTax += cc;
          } else {
            float cc = Float.parseFloat(cf2.getPrice()) * Float.parseFloat(cf2.getExchangeRate());
            if (cf2.getInvoicestate().equals("1")) {
              cc = SszgUtils.getInvoiceNum(cc, cf2.getDriverName());
            }
            yfTotalNoTax += cc;
          }
        }
      }
//      row.add(Utils.twoDot(ysTotalNoTax-yfTotalNoTax-10)+"");
      String numColumns = ",";
      for(int t = 0; t < feeNamesyf.size()+1; t ++) {
        numColumns += (t+5)+",";
      }
      setRow(xssfRow,row, cellStyle, numColumns);
    }

    // 合并单元格
    CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 5+0+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    cra =new CellRangeAddress(1, 1, 0, 5+0+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    cra =new CellRangeAddress(2, 2, 0, 5+0+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
    xssfSheet.addMergedRegion(cra);
    if (feeNamesyf.size() > 1) {
      cra = new CellRangeAddress(3, 3, 5, 4 + feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
      xssfSheet.addMergedRegion(cra);
    }

    for(int i = 0; i <6+feeNamesyf.size(); i ++) {
      if(i < 5 ||  i > (4+feeNamesyf.size())){
        cra =new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
      }
    }

    xssfRow = xssfSheet.createRow(5+list.size());
    //创建每个单元格Cell，即列的数据
    xssfCell = xssfRow.createCell(0); //创建单元格
    fontStyle = xssfWorkbook.createFont(); // 字体样式
    fontStyle.setBold(true); // 加粗
    fontStyle.setFontName("宋体"); // 字体
    fontStyle.setFontHeightInPoints((short) 10); // 大小
    cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
    cellStyle.setAlignment(HorizontalAlignment.RIGHT);
    // 将字体样式添加到单元格样式中
    cellStyle.setFont(fontStyle);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    xssfCell.setCellStyle(cellStyle);
    xssfCell.setCellValue("合计:"); //设置单元格内容


    for(int i =0; i <= feeNamesyf.size(); i ++) {
      xssfCell = xssfRow.createCell(5+i); //创建单元格
      fontStyle = xssfWorkbook.createFont(); // 字体样式
      fontStyle.setFontName("宋体"); // 字体
      fontStyle.setFontHeightInPoints((short) 10); // 大小
      cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 将字体样式添加到单元格样式中
      cellStyle.setFont(fontStyle);
      cellStyle.setBorderBottom(BorderStyle.THIN);
      cellStyle.setBorderLeft(BorderStyle.THIN);
      cellStyle.setBorderRight(BorderStyle.THIN);
      cellStyle.setBorderTop(BorderStyle.THIN);
      xssfCell.setCellStyle(cellStyle);
      xssfCell.setCellType(CellType.NUMERIC);
      xssfCell.setCellFormula("SUM("+letters[i]+"6:"+letters[i]+(list.size()+5)+")"); //设置单元格内容
    }

    cra =new CellRangeAddress(5+list.size(), 5+list.size(), 0, 4); // 起始行, 终止行, 起始列, 终止列
    RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
    RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
    xssfSheet.addMergedRegion(cra);

    OutputStream os = new FileOutputStream(new File(Constants.UPLOAD_DIR + "file/finance.xlsx"));
    //用输出流写到excel
    xssfWorkbook.write(os);
    os.flush();
    os.close();

    return JsonResult.error("excel 生成成功!");
  }

  public void setRow(XSSFRow xssfRow,List<String> row, XSSFCellStyle cellStyle, String changeNum) {
    //创建每个单元格Cell，即列的数据
    if (Utils.isNotEmpty(row)) {
      for (int i = 0; i < row.size(); i ++) {
        XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
        String str = row.get(i);
        if(str==null) {
          str = "";
        }
        //https://blog.csdn.net/weixin_29100399/article/details/114203311
        if(str.indexOf("function::") != -1) {
          xssfCell.setCellFormula(str.substring(10,str.length())); //设置单元格内容
        } else if(changeNum.indexOf(","+i+",")!=-1) {
          xssfCell.setCellType(CellType.NUMERIC);
          if(Utils.isNotEmpty(row.get(i))) {
            xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
          } else {
            xssfCell.setCellValue("0");
          }
        } else {
          xssfCell.setCellValue(row.get(i)); //设置单元格内容
        }
        if (cellStyle != null) {
          xssfCell.setCellStyle(cellStyle);
        }
      }
    }
  }

  public void setRowNoValue(XSSFRow xssfRow,List<String> row, XSSFCellStyle cellStyle, String changeNum) {
    //创建每个单元格Cell，即列的数据
    if (Utils.isNotEmpty(row)) {
      for (int i = 0; i < row.size(); i ++) {
        XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
        if (cellStyle != null) {
          xssfCell.setCellStyle(cellStyle);
        }
      }
    }
  }
}
